<?php

/**
 * CapchaDAO
 *
 */
class CapchaDAO
{
    public function insert($accountId, $siteId, $extension) {
        $db = Yii::app()->db;
        $deleteFlag = Constants::DELETE_FLAG_TRUE;

        $sql = 'INSERT INTO capcha(account_id,site_id,create_time, extension) VALUES (:accountId, :siteId, NOW(), :extension)';

        $command = $db->createCommand($sql);
        $command->bindParam(':accountId', $accountId);
        $command->bindParam(':siteId', $siteId);
        $command->bindParam(':extension', $extension);
        $command->execute();

        return Yii::app()->db->getLastInsertID();
    }

    public function findById($capchaId)
    {
        $db = Yii::app()->db;
        $deleteFlag = Constants::DELETE_FLAG_FALSE;

        $sql = 'SELECT captcha_id, account_id, site_id, content, status '
        . 'FROM capcha '
        . 'WHERE captcha_id = :captchaId AND delete_flag = :delete_flag ';

        $command = $db->createCommand($sql);
        $command->bindParam(':captchaId', $capchaId);
        $command->bindParam(':delete_flag', $deleteFlag);
        $result = $command->queryAll();

        if ($result)
            return $result[0];

        return null;
    }

    public function updateStatus($accountId , $status) {
        $db = Yii::app()->db;

        $sql = 'UPDATE capcha SET '
        . 'status = :status, update_time = NOW() '
        . 'WHERE account_id = :accountId ';

        $command = $db->createCommand($sql);
        $command->bindParam(':status', $status);
        $command->bindParam(':accountId', $accountId);
        $command->execute();

        return TRUE;
    }

    public function updateContent($capchaId , $content) {
        $db = Yii::app()->db;
        $answer = Constants::CAP_ANSWER;

        $sql = 'UPDATE capcha SET '
        . 'status = :status, content = :content, update_time = NOW() '
        . 'WHERE captcha_id = :capchaId ';

        $command = $db->createCommand($sql);
        $command->bindParam(':status', $answer);
        $command->bindParam(':content', $content);
        $command->bindParam(':capchaId', $capchaId);
        $command->execute();

        return TRUE;
    }

    public function findNewest() {
        $db = Yii::app()->db;
        $deleteFlag = Constants::DELETE_FLAG_FALSE;
        $newCap = Constants::CAP_NEW;

        $sql = 'SELECT captcha_id, account_id, site_id, content, status, extension, create_time '
             . 'FROM capcha '
             . 'WHERE content IS NULL AND delete_flag = :delete_flag AND status = :status '
             . 'AND create_time > NOW()- Interval 2 minute '
             . 'ORDER BY create_time ASC ';

        $command = $db->createCommand($sql);
        $command->bindParam(':delete_flag', $deleteFlag);
        $command->bindParam(':status', $newCap);
        $result = $command->queryAll();

        if ($result)
            return $result[0];

        return null;
    }

}